/*


* Conditions Of Use
*
* This software was developed by employees of the Sigmatrix(Beijing) Corporation.
* This software is provided by sigmatrix as a service and is expressly
* provided "AS IS."  Sigmatrix MAKES NO WARRANTY OF ANY KIND, EXPRESS, IMPLIED
* OR STATUTORY, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTY OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, NON-INFRINGEMENT
* AND DATA ACCURACY.  Sigmatrix does not warrant or make any representations
* regarding the use of the software or the results thereof, including but
* not limited to the correctness, accuracy, reliability or usefulness of
* the software.
*
* Permission to use this software is contingent upon your acceptance
* of the terms of this agreement.
*
*/
package com.sigmatrix.h5.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONObject;
import com.sigmatrix.h5.dao.DisDealerAcctDao;
import com.sigmatrix.h5.dao.DisDealerDao;
import com.sigmatrix.h5.dao.DisRebateAuditDao;
import com.sigmatrix.h5.dao.DisRebateScanCodeDao;
import com.sigmatrix.h5.dao.DisRebateScanCodeErrorDao;
import com.sigmatrix.h5.dao.DisTerminalShopDao;
import com.sigmatrix.h5.dao.ProductDao;
import com.sigmatrix.h5.entity.DisRebateScanCode;
import com.sigmatrix.h5.enums.CrmAcctType;
import com.sigmatrix.h5.utils.CommonUtils;
import com.sigmatrix.h5.utils.Constants;
import com.sigmatrix.h5.utils.SimpleRowMapper;
import com.sigmatrix.h5.web.args.DealerInventoryPageParam;
import com.sigmatrix.h5.web.mode.DealerInventoryData;
import com.sigmatrix.h5.web.mode.DealerInventoryDayCount;
import com.sigmatrix.sc.jdbc.common.dao.DaoImpl;
import com.sigmatrix.sc.jdbc.common.lang.Page;

/**
 *@ClassName: DisRebateScanCodeDaoImpl
 *@Description: 
 *@author MH
 *@date 2017年8月14日 下午6:36:46
 */
@Repository
public class DisRebateScanCodeDaoImpl extends DaoImpl<DisRebateScanCode,Integer> implements DisRebateScanCodeDao{
	@Autowired
	private DisDealerAcctDao disDealersAcctDao;
	
	@Autowired
	private ProductDao productDao;
	
	@Autowired
	private DisDealerDao disDealerDao;
	
	@Autowired
	private DisTerminalShopDao disTerminalShopDao;
	
	@Autowired
	private DisRebateAuditDao disRebateAuditDao;
	
	@Autowired
	private DisRebateScanCodeErrorDao disRebateScanCodeErrorDao;
	
	@Override
	public Integer selectCountByDealerAcctsIdAndActivityId(String acct, Integer activityId) {
		final StringBuffer sql = new StringBuffer();
		final String acctTableName = disDealersAcctDao.tableName();
		sql.append("select COUNT(1) from ").append(tableName()).append(" a");
		sql.append(" where a._account in ( select b._account from ").append(acctTableName).append(" b WHERE b._dealer_id in (");
		sql.append(" select c._dealer_id from ").append(acctTableName).append(" c where c._account = ? ");
		sql.append(")) and a._rebate_activity_id = ?");
		logger.info(sql.toString());
		logger.info("param=["+acct+","+activityId+"]");
		return jdbcTemplate.queryForObject(sql.toString(), Integer.class, acct,activityId);
	}

	@Override
	public Integer selectCountByDealerIdAndActivityId(Integer dealerId, Integer activityId) {
		DisRebateScanCode vo = new DisRebateScanCode();
		vo.setDealerId(dealerId);
		vo.setRebateActivityId(activityId);
		vo.setStatus(Constants.STATUS_ENABLE);
		return selectCount(vo);
	}

	@Override
	public Page<DealerInventoryData> findPage(DealerInventoryPageParam vo) {
		List<Object> values = new ArrayList<Object>();
		Map<String, String> order = new HashMap<>();
		order.put("_scan_time_day", "DESC");
		//为selectDataSql填充
		if(CommonUtils.isNotEmpty(vo.getStartTime()) ) {
			values.add(vo.getStartTime());
		}
		if(CommonUtils.isNotEmpty(vo.getEndTime()) ) {
			values.add(vo.getEndTime());
		}
		//为selectErrorSql填充
		if(CommonUtils.isNotEmpty(vo.getStartTime()) ) {
			values.add(vo.getStartTime());
		}
		if(CommonUtils.isNotEmpty(vo.getEndTime()) ) {
			values.add(vo.getEndTime());
		}
		//为selectErrorCodeSql填充
		if(CommonUtils.isNotEmpty(vo.getStartTime()) ) {
			values.add(vo.getStartTime());
		}
		if(CommonUtils.isNotEmpty(vo.getEndTime()) ) {
			values.add(vo.getEndTime());
		}
		return query(getSelectSql(vo), order, vo.getPageNo(), vo.getPageSize(), SimpleRowMapper.createDefault(DealerInventoryData.class), values.toArray());
	}
	private String getSelectSql(DealerInventoryPageParam vo) {
		StringBuffer sql = new StringBuffer(); 
		String selectDataSql = getSelectDataSql(vo);
		String selectErrorSql = disRebateScanCodeErrorDao.getSelectErrorSql(vo);
		String selectErrorCodeSql = disRebateScanCodeErrorDao.getSelectErrorCodeSql(vo);
		sql.append("SELECT SUM (b._count) AS _success_count, SUM (b._price) AS _price, SUM (b._error_num) AS _error_count, b._scan_time_day,");
		sql.append("b._product_name, b._scan_id, b._scan_name FROM (");
		sql.append(selectDataSql).append(" UNION ").append(selectErrorSql).append(" UNION ").append(selectErrorCodeSql).append(" ) b ");
		sql.append(" WHERE 1 = 1 GROUP BY b._scan_time_day, b._scan_id, b._product_name, b._scan_name ");
		return sql.toString();
	}

	public Integer countByOpenIdInHoure(String openId, Integer activityId, Date firstScanTime, int period) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) from ").append(tableName());
		sql.append(" where 1=1 ");
		sql.append(" and _scan_time >= ? and _scan_time <= ?");
		//sql.append(" and _rebate_activity_id = ?");
		sql.append(" and _open_id = ?");
		return queryForObject(sql.toString(), Integer.class, firstScanTime,new Date(firstScanTime.getTime()+1000*60*60*period),openId);
	}

	@Override
	public List<DisRebateScanCode> findScanHistory2_6(String qrCode, String qrCode2) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(tableName());
		sql.append(" where _qr_code in(?,?)");
		sql.append(" and _status = ").append(Constants.STATUS_ENABLE);
		sql.append(" order by _scan_time desc");
		return query(sql.toString(), new DefaultRowMapper(), qrCode,qrCode2);
	}

	@Override
	public List<DealerInventoryDayCount> findDayCount(DealerInventoryPageParam vo) {
		return query(getSelectDataSql(vo),SimpleRowMapper.createDefault(DealerInventoryDayCount.class),vo.getStartTime(),vo.getEndTime());
	}

	private String getSelectDataSql(DealerInventoryPageParam vo) {
		StringBuffer sql = new StringBuffer();
		final String dealerTableName = disDealerDao.tableName();
		final String shopTableName = disTerminalShopDao.tableName();
		final String scanCodeTableName = tableName();
		final String auditTableName =disRebateAuditDao. tableName();
		final String productTableName = productDao.tableName();
		String dealerIdsString = new String();
		if(CommonUtils.isNotEmpty(vo.getDealerType())) {
			if(CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
				List<Integer> dealersId = disDealerDao.findFirstChildIdAnSelf(vo.getDealerId());
				dealerIdsString = StringUtils.join(dealersId.toArray(), ",");
			}else if(CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())) {
				dealerIdsString = vo.getDealerId()+"";
			}
		}
		sql.append("SELECT COUNT (DISTINCT(code._id)) AS _count, code._scan_id AS _scan_id , ");
		sql.append("product._name AS _product_name,");
		sql.append( "(CASE WHEN shop._name IS NULL THEN dealer._name ELSE shop._name END)  AS _scan_name,");
		sql.append( "convert(char(10),code._scan_time,120) AS _scan_time_day , SUM(audit._amount ) AS _price , 0 AS _error_num FROM ");
		sql.append(productTableName).append(" product ,");
		sql.append(auditTableName).append(" audit ,  ");
		sql.append(scanCodeTableName).append(" code ");
		sql.append(" LEFT JOIN ( SELECT _name ,_id FROM ").append(dealerTableName);
		sql.append(" )dealer ON ( code._scan_id = dealer._id AND code._scan_type = 2 )");
		sql.append(" LEFT JOIN ( SELECT _name ,_id FROM ").append(shopTableName);
		sql.append(" )shop ON ( code._scan_id = shop._id AND code._scan_type = 3 ) WHERE");
		if (CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
			sql.append("  ( code._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND code._scan_type= ").append(vo.getDealerType());
			sql.append(" OR code._dealer_id = ").append(vo.getDealerId()).append("  AND code._scan_type = 3) ");
		}else if (CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())){
			sql.append("  code._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND code._scan_type= ").append(vo.getDealerType());
		}
		sql.append(" AND audit._scan_code_id = code._id ");
		sql.append(" AND audit._dealer_id= ").append(vo.getDealerId());
		sql.append(" AND code._product_id = product._id ");
		if(CommonUtils.isNotEmpty(vo.getStartTime()) && CommonUtils.isNotEmpty(vo.getEndTime())) {
			sql.append(" AND code._scan_time BETWEEN ?  and  ? ");
		}else {
			if(CommonUtils.isNotEmpty(vo.getStartTime())) {
				sql.append(" AND code._scan_time > ? ");
			}else if(CommonUtils.isNotEmpty(vo.getEndTime())) {
				sql.append(" AND code._scan_time < ? ");
			}
		}
		sql.append(" GROUP BY convert(char(10),code._scan_time,120), code._scan_id, product._name, (CASE WHEN shop._name IS NULL THEN dealer._name ELSE shop._name END)  ");
		return sql.toString();
	}

	@Override
	public BigDecimal findPriceTotal(DealerInventoryPageParam vo) {
		StringBuffer sql = new StringBuffer();
		List<Object> values = new ArrayList<Object>();
		final String scanCodeTableName = tableName();
		final String auditTableName =disRebateAuditDao. tableName();
		String dealerIdsString = new String();
		if(CommonUtils.isNotEmpty(vo.getDealerType())) {
			if(CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
				List<Integer> dealersId = disDealerDao.findFirstChildIdAnSelf(vo.getDealerId());
				dealerIdsString = StringUtils.join(dealersId.toArray(), ",");
			}else if(CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())) {
				dealerIdsString = vo.getDealerId()+"";
			}
		}
		sql.append("SELECT SUM (audit._amount) AS _price FROM ");
		sql.append(auditTableName).append(" audit, ");
		sql.append(scanCodeTableName).append(" code ");
		sql.append(" where 1= 1 ");
		if (CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
			sql.append("  AND ( code._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND code._scan_type= ").append(vo.getDealerType());
			sql.append(" OR code._dealer_id = ").append(vo.getDealerId()).append("  AND code._scan_type = 3) ");
		}else if (CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())){
			sql.append("  AND code._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND code._scan_type= ").append(vo.getDealerType());
		}
		sql.append(" AND audit._scan_code_id = code._id ");
		sql.append(" AND audit._dealer_id= ").append(vo.getDealerId());
		if(CommonUtils.isNotEmpty(vo.getStartTime()) && CommonUtils.isNotEmpty(vo.getEndTime())) {
			sql.append(" AND code._scan_time BETWEEN ?  and  ? ");
			values.add(vo.getStartTime());
			values.add(vo.getEndTime());
		}else {
			if(CommonUtils.isNotEmpty(vo.getStartTime())) {
				sql.append(" AND code._scan_time > ? ");
				values.add(vo.getStartTime());
			}else if(CommonUtils.isNotEmpty(vo.getEndTime())) {
				sql.append(" AND code._scan_time < ? ");
				values.add(vo.getEndTime());
			}
		}
		logger.info(sql.toString());
		logger.info("param="+JSONObject.toJSONString(values));
		return jdbcTemplate.queryForObject(sql.toString(), BigDecimal.class, values.toArray());
	}
	
}
